<?php

class Db {

    private $_host;
    private $_database;
    private $_user;
    private $_pass;
    private $_db;

    /**
     * Constructure
     */
    public function __construct($host, $database, $user, $pass) {
        $this->_host = $host;
        $this->_database = $database;
        $this->_user = $user;
        $this->_pass = $pass;
    }

    /**
     * Get project
     * @author tien.nguyen
     * @return array|null
     */
    function getProject($projectId) {
        $query = "SELECT * FROM projects 
        INNER JOIN frameworks ON frameworks.framework_id = projects.framework_id
        WHERE project_id = " . $projectId;
        $res = mysql_query($query);
        $row = mysql_fetch_assoc($res);
        if (is_array($row)) {
            return $row;
        }
        return null;
    }

    /**
     * get forms from database
     * @param array $form
     * @param array $elements
     * @author tien.nguyen
     * @return array
     */
    public function getForms($projectId) {
        $array = array();
        $query = "SELECT * FROM forms 
        WHERE project_id = " . $projectId;
        $res = mysql_query($query);
        while ($row = mysql_fetch_object($res)) {
            array_push($array, $row);
        }
        return $array;
    }

    /**
     * get forms by action id
     * @param int $actionId
     * @return array
     */
    public function getFormsByActionId($actionId) {
        $array = array();
        $query = "SELECT * FROM forms 
        JOIN form_types ON form_types.form_type_name = forms.form_extends
        WHERE action_id = " . $actionId . "
        ORDER BY form_order ASC";
        $res = mysql_query($query);
        while ($row = mysql_fetch_object($res)) {
            array_push($array, $row);
        }
        return $array;
    }

    /**
     * Get form type by name
     * @author tien.nguyen
     * @param string $name
     */
    public function getFormTypeByName($name) {
        $query = "SELECT * FROM form_types 
        WHERE form_type_name = '" . $name . "'";
        $res = mysql_query($query);
        $row = mysql_fetch_assoc($res);
        if (is_array($row)) {
            return $row;
        }
        return null;
    }

    /**
     * get element form
     * @param int $formId
     * @return array
     */
    public function getElements($formId) {
        $array = array();
        $query = "SELECT * FROM elements 
        INNER JOIN element_types ON elements.element_type_id = element_types.element_type_id
        WHERE elements.form_id = " . $formId . "
        AND (element_parent IS NULL OR element_parent  = '')
        ORDER BY position ASC";
        $res = mysql_query($query);
        while ($row = mysql_fetch_object($res)) {
            $this->getElementChilds($row);
            $row->params = $this->getElementParams($row->element_id);
            array_push($array, $row);
        }
                
        return $array;
    }

    /**
     * 
     * @param type $element
     * @return array 
     */
    public function getElementChilds(&$element) {
        $array = array();
        $query = "SELECT * FROM elements 
        INNER JOIN element_types ON elements.element_type_id = element_types.element_type_id        
        AND element_parent = " . $element->element_id . "
        ORDER BY position ASC";
        $res = mysql_query($query);
        while ($row = mysql_fetch_object($res)) {
            $this->getElementChilds($row);
            $row->params = $this->getElementParams($row->element_id);
            array_push($array, $row);
            
        }
        
        $element->childs = $array;        
    }
    
    /**
    * Get elemnt params
    * @author tien.nguyen
    * 
    * @param mixed $elementId
    */
    public function getElementParams($elementId){
        $array = array();
        $query = "SELECT * FROM element_params where element_id = ".$elementId;
        $res = mysql_query($query);
        while ($row = mysql_fetch_object($res)) {
            array_push($array, $row);
            
        }
        return $array;
    }

    /**
     * Get schema
     * @author tien.nguyen
     * @return array
     */
    public function getSchema($dbName) {
        $res = array();
        $selectDb = mysql_select_db($dbName);
        $query = "SELECT * FROM information_schema WHERE TABLE_SCHEMA = '" . $dbName . "'";
        $result = mysql_query($query);
        while ($row = mysql_fetch_object($result)) {
            array_push($res, $row);
        }
        $selectDb = mysql_select_db($this->_database);

        return $res;
    }

    /**
     * Get general controller
     * @author tien.nguyen
     */
    public function getControllers($projectId) {
        $res = array();
        $query = "SELECT * FROM controllers WHERE project_id = " . $projectId;
        $result = mysql_query($query);
        while ($row = mysql_fetch_object($result)) {
            array_push($res, $row);
        }
        return $res;
    }

    /**
     * General action
     * @author tien.nguyen
     */
    public function getActions($controllerId) {
        $res = array();
        $query = "SELECT * FROM actions
        JOIN action_types ON actions.action_type_id = action_types.action_type_id
        WHERE controller_id = " . $controllerId;
        $result = mysql_query($query);
        while ($row = mysql_fetch_object($result)) {
            array_push($res, $row);
        }
        
        return $res;
    }

    /**
     * Get param action
     * @author tien.nguyen
     */
    public function getParamAction($actionId) {
        $res = array();
        $query = "SELECT * FROM action_params
        WHERE action_id = " . $actionId;
        $result = mysql_query($query);
        while ($row = mysql_fetch_object($result)) {
            array_push($res, $row);
        }
        return $res;
    }

    /**
     * Get action default
     * @param int $controllerId
     * @return array
     */
    public function getActionDefault($controllerId) {
        $res = array();
        $query = "SELECT * FROM actions 
        JOIN action_types ON action_types.action_type_id = actions.action_type_id
        WHERE action_default = 1 AND controller_id = " . $controllerId;
        $result = mysql_query($query);
        $res = mysql_fetch_assoc($result);
        return $res;
    }

    /**
     * Get menus for project
     * @author tien.nguyen
     */
    public function getMenus($projectId) {
        $res = array();
        $query = "SELECT * FROM menus 
        LEFT JOIN actions ON actions.action_id = menus.action_id
        LEFT JOIN action_types ON action_types.action_type_id = actions.action_type_id
        LEFT JOIN controllers ON controllers.controller_id = menus.controller_id
        WHERE menus.project_id = " . $projectId;
        $result = mysql_query($query);
        //general row    
        while ($row = mysql_fetch_object($result)) {
            array_push($res, $row);
        }

        return $res;
    }

    /**
     * Get form full by table name
     * @author tien.nguyen
     * 
     * @param string $tableName
     */
    public function getFormFullByTableName($tableName) {
        $res = array();

        $query = "SELECT * FROM forms 
        JOIN form_types ON form_types.form_type_name = forms.form_extends
        JOIN queries ON queries.query_id = forms.query_id
        JOIN query_tables ON query_tables.query_id = queries.query_id
        WHERE query_tables.query_table_name = '" . $tableName . "'";

        $result = mysql_query($query);
        while ($row = mysql_fetch_object($result)) {
            array_push($res, $row);
        }

        return $res;
    }

    /**
     * Get query by query id
     * @author tien.nguyen
     * @param int $queryId
     */
    public function getQueryId($queryId) {
        $query = "SELECT * FROM queries WHERE queries.query_id = " . $queryId;
        $result = mysql_query($query);
        $res = mysql_fetch_assoc($result);
        return $res;
    }

    /**
     * get query by table name
     * @author tien.nguyen
     * 
     * @param string $tableName
     * @return array
     */
    public function getQueryByTableName($tableName) {
        $res = array();

        //sql select by table nam
        $query = "SELECT * FROM queries JOIN query_tables
        ON query_tables.query_id = queries.query_id 
        WHERE query_tables.query_table_name = '" . $tableName . "'";

        $result = mysql_query($query);
        while ($row = mysql_fetch_object($result)) {
            array_push($res, $row);
        }

        return $res;
    }

    /**
     * get query select by query id
     * @param int queryId
     * @author tien.nguyen
     */
    public function getQuerySelect($queryId) {
        $res = array();

        $query = "SELECT * FROM query_selects WHERE query_id = " . $queryId;

        $result = mysql_query($query);
        while ($row = mysql_fetch_object($result)) {
            array_push($res, $row);
        }

        return $res;
    }

    /**
     * get query where
     * @author tien.nguyen
     * @param int $queryId
     */
    public function getQueryWhere($queryId) {
        $res = array();

        $query = "SELECT * FROM query_wheres WHERE query_id = " . $queryId;

        $result = mysql_query($query);
        while ($row = mysql_fetch_object($result)) {
            array_push($res, $row);
        }

        return $res;
    }

    /**
     * get query table join
     * @author tien.nguyen
     * @param int $queryId
     */
    public function getQueryTableJoin($queryId) {
        $res = array();

        $query = "SELECT * FROM query_table_joins WHERE query_table_joins.query_id = " . $queryId;

        $result = mysql_query($query);
        while ($row = mysql_fetch_object($result)) {
            array_push($res, $row);
        }

        return $res;
    }

    /**
     * get query table
     * @author tien.nguyen
     * @param int $queryId
     */
    public function getQueryTable($queryId) {
        $query = "SELECT * FROM query_tables 
        JOIN tables ON tables.table_name = query_tables.query_table_name
        WHERE query_tables.query_id = " . $queryId;
        $result = mysql_query($query);
        $res = mysql_fetch_assoc($result);
        return $res;
    }

    /**
     * get query order
     * @author tien.nguyen
     * @param int $queryId
     */
    public function getQueryOrder($queryId) {
        $res = array();

        $query = "SELECT * FROM query_orders WHERE query_orders.query_id = " . $queryId;

        $result = mysql_query($query);
        while ($row = mysql_fetch_object($result)) {
            array_push($res, $row);
        }

        return $res;
    }

    /**
     * get tables
     * @author tien.nguyen
     * @param int $queryId
     */
    public function getTables() {
        $res = array();

        $query = "SELECT * FROM tables";

        $result = mysql_query($query);
        while ($row = mysql_fetch_object($result)) {
            array_push($res, $row);
        }

        return $res;
    }

    /**
     * Open database
     * @author tien.nguyen
     */
    public function open() {
        $this->_db = mysql_connect($this->_host, $this->_user, $this->_pass);
        if (!$this->_db) {
            die("Can't connect server, host, user, pass is wrong.");
        }

        $selectDb = mysql_select_db($this->_database, $this->_db);
        mysql_query("SET character_set_results = 'utf8', 
        character_set_client = 'utf8', 
        character_set_connection = 'utf8', 
        character_set_database = 'utf8', 
        character_set_server = 'utf8'", $this->_db);
        return $this->_db;
    }

    /**
     * close database
     * @author tien.nguyen
     */
    public function close() {
        mysql_close($this->_db);
    }

}